﻿Public Class SROOMITEMCLASS
    Dim connect As CONDB = CONDB.NewConnection()

    Private bedsid = Nothing
    Private clinic = Nothing
    Private sroomitemPrdcode = Nothing
    Private bedscode = Nothing
    Private bedsname = Nothing
    'Private sroomitemStatus = Nothing
    'Private f_admit = Nothing
    'Private f_ready = Nothing
    'Private f_amiss = Nothing
    'Private f_clean = Nothing
    Private stid = Nothing
    Private remark = Nothing
    Private masproductSQL = Nothing

    Private selectSroomitem = "SELECT sri.`bedsid`, sri.`clinic`, mc.clinicname, sri.`prdcode`, sri.`bedscode`, sri.`bedsname`, sri.`remark`, mpd.`prdname`, mpd.`prdcat`, mpc.`catname`, mpd.`ccode`, c1.`name` AS 'cname',  mpd.`rcode`, c2.`name` AS 'rname',mpd.`iccode`, c3.`name` AS 'icname',  mpd.`ircode`, c4.`name` AS 'irname',  mpd.`hccode`, c5.`name` AS 'hcname',  mpd.`hrcode`, c6.`name` AS 'hrname',  mpd.`b_unit`, mu1.unitname_th AS 'buyUnit', mpd.`s_unit`, mu2.unitname_th AS 'sellUnit', mpd.`ipdprc`, mpd.`status` FROM sroomitem sri LEFT JOIN masproduct mpd ON sri.prdcode = mpd.prdcode LEFT JOIN masclinic mc ON sri.clinic = mc.clinic LEFT JOIN masprdcats mpc ON mpd.prdcat = mpc.catname LEFT JOIN finchcode c1 ON mpd.ccode = c1.ccode LEFT JOIN finrecode c2 ON mpd.rcode = c2.rcode LEFT JOIN finchcode c3 ON mpd.iccode = c3.ccode LEFT JOIN finrecode c4 ON mpd.ircode = c4.rcode LEFT JOIN finchcode c5 ON mpd.hccode = c5.ccode LEFT JOIN finrecode c6 ON mpd.hrcode = c6.rcode LEFT JOIN masunit mu1 ON mpd.b_unit = mu1.unitid LEFT JOIN masunit mu2 ON mpd.b_unit = mu2.unitid "

    Property selectSroomitem_ As String
        Get
            Return selectSroomitem
        End Get
        Set(value As String)
            selectSroomitem = value
        End Set
    End Property

    Property bedsid_ As Integer
        Get
            Return bedsid
        End Get
        Set(value As Integer)
            bedsid = value
        End Set
    End Property

    Property clinic_ As Integer
        Get
            Return clinic
        End Get
        Set(value As Integer)
            clinic = value
        End Set
    End Property

    Property sroomitemPrdcode_ As Integer
        Get
            Return sroomitemPrdcode
        End Get
        Set(value As Integer)
            sroomitemPrdcode = value
        End Set
    End Property

    Property bedscode_ As String
        Get
            Return bedscode
        End Get
        Set(value As String)
            bedscode = value
        End Set
    End Property

    Property bedsname_ As String
        Get
            Return bedsname
        End Get
        Set(value As String)
            bedsname = value
        End Set
    End Property

    'Property sroomitemStatus_ As Boolean
    '    Get
    '        Return sroomitemStatus
    '    End Get
    '    Set(value As Boolean)
    '        If value = True Then
    '            sroomitemStatus = 1
    '        Else
    '            sroomitemStatus = 0
    '        End If
    '    End Set
    'End Property

    'Property f_admit_ As Boolean
    '    Get
    '        Return f_admit
    '    End Get
    '    Set(value As Boolean)
    '        If value = True Then
    '            f_admit = 1
    '        Else
    '            f_admit = 0
    '        End If
    '    End Set
    'End Property

    'Property f_ready_ As Boolean
    '    Get
    '        Return f_ready
    '    End Get
    '    Set(value As Boolean)
    '        If value = True Then
    '            f_ready = 1
    '        Else
    '            f_ready = 0
    '        End If
    '    End Set
    'End Property

    'Property f_amiss_ As Boolean
    '    Get
    '        Return f_amiss
    '    End Get
    '    Set(value As Boolean)
    '        If value = True Then
    '            f_amiss = 1
    '        Else
    '            f_amiss = 0
    '        End If
    '    End Set
    'End Property

    'Property f_clean_ As Boolean
    '    Get
    '        Return f_clean
    '    End Get
    '    Set(value As Boolean)
    '        If value = True Then
    '            f_clean = 1
    '        Else
    '            f_clean = 0
    '        End If
    '    End Set
    'End Property

    Property stid_ As Integer
        Get
            Return stid
        End Get
        Set(value As Integer)
            stid = value
        End Set
    End Property

    Property remark_ As String
        Get
            Return remark
        End Get
        Set(value As String)
            remark = value
        End Set
    End Property

    Property masproductSQL_ As String
        Get
            Return masproductSQL
        End Get
        Set(value As String)
            masproductSQL = value
        End Set
    End Property

    Public Function insertSROOMITEM() As Boolean
        If connect.GetTable("SELECT bedsid FROM sroomitem WHERE bedscode = '" & bedscode & "'").Rows.Count > 0 Then
            MsgBox("ไม่สามารถใช้รหัสเตียงดังกล่าวได้ เนื่องจากมีรหัสดังกล่าวอยู่แล้ว")
            Return False
        Else
            Try
                connect.BeginTrans()

                masproductSQL += " SELECT LAST_INSERT_ID();"

                Dim prdcode As String
                prdcode = connect.ExecuteScalar(masproductSQL)

                Dim sql1 As String = ""
                Dim sql2 As String = ""
                Dim sql3 As String = ""

                If clinic IsNot Nothing Then
                    sql2 += "clinic, "
                    sql3 += "'" & clinic & "', "
                Else
                    sql2 += "clinic, "
                    sql3 += "default, "
                End If

                If prdcode IsNot Nothing Then
                    sql2 += "prdcode, "
                    sql3 += "'" & prdcode & "', "
                Else
                    sql2 += "prdcode, "
                    sql3 += "default, "
                End If

                If bedscode IsNot Nothing Then
                    sql2 += "bedscode, "
                    sql3 += "'" & bedscode & "', "
                Else
                    sql2 += "bedscode, "
                    sql3 += "default, "
                End If

                If bedsname IsNot Nothing Then
                    sql2 += "bedsname, "
                    sql3 += "'" & bedsname & "', "
                Else
                    sql2 += "bedsname, "
                    sql3 += "default, "
                End If

                sql2 += "f_admit, "
                sql3 += "default, "

                sql2 += "f_ready, "
                sql3 += "default, "

                sql2 += "f_amiss, "
                sql3 += "default, "

                sql2 += "f_clean, "
                sql3 += "default, "

                sql2 += "stid, "
                sql3 += "'" & stid & "', "


                If remark IsNot Nothing Or remark <> "" Then
                    sql2 += "remark"
                    sql3 += "'" & remark & "'"
                End If

                sql1 += "INSERT INTO sroomitem (" & sql2 & ") VALUES (" & sql3 & ");"

                connect.ExecuteNonQuery(sql1)
                connect.CommitTrans()
                MsgBox("เพิ่มข้อมูลเตียงสำเร็จแล้ว")
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            Finally
                connect.Dispose()
            End Try
        End If
    End Function

    Public Function editSROOMITEM() As Boolean
        Try
            connect.BeginTrans()

            Dim sql As String = ""
            sql += "UPDATE sroomitem SET "

            If clinic Is Nothing Then
                sql += "clinic = null, "
            Else
                sql += "clinic = '" & clinic & "', "
            End If

            sql += "bedscode = '" & bedscode & "', "
            sql += "bedsname = '" & bedsname & "', "
            'sql += "status = '" & sroomitemStatus & "', "
            'sql += "f_admit = '" & sroomitemStatus & "', "
            'sql += "f_ready = '" & sroomitemStatus & "', "
            'sql += "f_amiss = '" & sroomitemStatus & "', "
            'sql += "f_clean = '" & sroomitemStatus & "', "
            sql += "stid = '" & stid & "', "
            sql += "remark = '" & remark & "' "

            sql += "WHERE bedsid = '" & bedsid & "'"

            connect.ExecuteNonQuery(sql)
            connect.ExecuteNonQuery(masproductSQL)
            connect.CommitTrans()
            MsgBox("แก้ไขข้อมูลเตียงสำเร็จแล้ว")
            Return True
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox("ไม่สามารถแก้ไขข้อมูลได้" & ex.ToString)
            Return False
        Finally
            connect.Dispose()
        End Try
    End Function

End Class
